package com.eye.catcher.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.eye.catcher.entity.TSsDaStatisticsInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.eye.catcher.system.yw.statistics.vo.ObserveHasCreateInfoVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author huzhiwei
 * @since 2022-08-03
 */
@Mapper
public interface TSsDaStatisticsInfoMapper extends BaseMapper<TSsDaStatisticsInfo> {

    String querySql = " select (@i:= @i+1) id, " +
            "    baseId BASE_ID, " +
            "    baseName BASE_NAME, " +
            "    observeName OBSERVE_NAME, " +
            "    annual," +
            "    type, " +
            "    ave_mainStemLeafAge MAIN_STEM_LEAF_AGE, " +
            "    ave_individualLevel INDIVIDUAL_LEVEL, " +
            "    ave_stemsNumber STEMS_NUMBER, " +
            "    ave_singlePlantDefectiveNumber SINGLE_PLANT_DEFECTIVE_NUMBER " +
            " from vw_observe_statistics_info si,(select @i:=0) b ";

    String wrapperSql = " select * from (" + querySql + ") as q ${ew.customSqlSegment}";
    @Select(wrapperSql)
    IPage<TSsDaStatisticsInfo> statisticsPage(Page<TSsDaStatisticsInfo> page,@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);
    @Select(wrapperSql)
    List<TSsDaStatisticsInfo> statisticsList(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String countWrapperSql = " select count(*) from (" + querySql + ") as q ${ew.customSqlSegment}";
    @Select(countWrapperSql)
    long statisticsCount(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String plantingQuerySql = " select (@i:= @i+1) id, " +
            "    cityName, " +
            "    subordinateCounties, " +
            "    annual," +
            "    observeName OBSERVE_NAME, " +
            "    sum_mainStemLeafAge MAIN_STEM_LEAF_AGE, " +
            "    sum_individualLevel INDIVIDUAL_LEVEL, " +
            "    sum_stemsNumber STEMS_NUMBER, " +
            "    sum_singlePlantDefectiveNumber SINGLE_PLANT_DEFECTIVE_NUMBER " +
            " from vw_observe_planting_statistics_info si,(select @i:=0) b ";

    String plantingWrapperSql = " select * from (" + plantingQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(plantingWrapperSql)
    IPage<TSsDaStatisticsInfo> plantingStatisticsPage(Page<TSsDaStatisticsInfo> page,@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);
    @Select(plantingWrapperSql)
    List<TSsDaStatisticsInfo> plantingStatisticsList(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String plantingCountWrapperSql = " select count(*) from (" + plantingQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(plantingCountWrapperSql)
    long plantingStatisticsCount(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);


    String plantingTypeQuerySql = " select (@i:= @i+1) id, " +
            "    cityName, " +
            "    subordinateCounties, " +
            "    annual," +
            "    type," +
            "    observeName OBSERVE_NAME, " +
            "    sum_mainStemLeafAge MAIN_STEM_LEAF_AGE, " +
            "    sum_individualLevel INDIVIDUAL_LEVEL, " +
            "    sum_stemsNumber STEMS_NUMBER, " +
            "    sum_singlePlantDefectiveNumber SINGLE_PLANT_DEFECTIVE_NUMBER " +
            " from vw_observe_planting_type_statistics_info si,(select @i:=0) b ";

    String plantingTypeWrapperSql = " select * from (" + plantingTypeQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(plantingTypeWrapperSql)
    IPage<TSsDaStatisticsInfo> plantingTypeStatisticsPage(Page<TSsDaStatisticsInfo> page,@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);
    @Select(plantingTypeWrapperSql)
    List<TSsDaStatisticsInfo> plantingTypeStatisticsList(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String plantingTypeCountWrapperSql = " select count(*) from (" + plantingTypeQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(plantingTypeCountWrapperSql)
    long plantingTypeStatisticsCount(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);



    String regionQuerySql = " select (@i:= @i+1) id, " +
            "    region, " +
            "    total, " +
            "    annual, " +
            "    observeName OBSERVE_NAME, " +
            "    ave_mainStemLeafAge MAIN_STEM_LEAF_AGE, " +
            "    ave_individualLevel INDIVIDUAL_LEVEL, " +
            "    ave_stemsNumber STEMS_NUMBER, " +
            "    ave_singlePlantDefectiveNumber SINGLE_PLANT_DEFECTIVE_NUMBER " +
            " from vw_observe_region_statistics_info si,(select @i:=0) b ";

    String regionWrapperSql = " select * from (" + regionQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(regionWrapperSql)
    IPage<TSsDaStatisticsInfo> regionStatisticsPage(Page<TSsDaStatisticsInfo> page,@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);
    @Select(regionWrapperSql)
    List<TSsDaStatisticsInfo> regionStatisticsList(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String regionCountWrapperSql = " select count(*) from (" + regionQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(regionCountWrapperSql)
    long regionStatisticsCount(@Param("ew")  QueryWrapper<TSsDaStatisticsInfo> wrapper);



    String regionTypeQuerySql = " select (@i:= @i+1) id, " +
            "    region, " +
            "    total, " +
            "    annual, " +
            "    type, " +
            "    observeName OBSERVE_NAME, " +
            "    ave_mainStemLeafAge MAIN_STEM_LEAF_AGE, " +
            "    ave_individualLevel INDIVIDUAL_LEVEL, " +
            "    ave_stemsNumber STEMS_NUMBER, " +
            "    ave_singlePlantDefectiveNumber SINGLE_PLANT_DEFECTIVE_NUMBER " +
            " from vw_observe_region_type_statistics_info si,(select @i:=0) b ";

    String regionTypeWrapperSql = " select * from (" + regionTypeQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(regionTypeWrapperSql)
    IPage<TSsDaStatisticsInfo> regionTypeStatisticsPage(Page<TSsDaStatisticsInfo> page,@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);
    @Select(regionTypeWrapperSql)
    List<TSsDaStatisticsInfo> regionTypeStatisticsList(@Param("ew") QueryWrapper<TSsDaStatisticsInfo> wrapper);

    String regionTypeCountWrapperSql = " select count(*) from (" + regionTypeQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(regionTypeCountWrapperSql)
    long regionTypeStatisticsCount(@Param("ew")  QueryWrapper<TSsDaStatisticsInfo> wrapper);


    String observeHasCreateQuerySql = " select (@i:= @i+1) id, " +
            " osc.BASE_ID baseId,osc.BASE_CODE baseCode,osc.BASE_NAME baseName,  " +
            " osc.FIELD_ID fieldId,osc.FIELD_CODE fieldCode,osc.FIELD_NAME fieldName," +
            " osc.OBSERVE_NAME observeName,osc.count  " +
            " FROM vw_observe_has_create osc,(select @i:=0) b  " +
            " ORDER BY " +
            " cast( osc.BASE_CODE AS UNSIGNED ), " +
            " cast( osc.OBSERVE_ORDER AS UNSIGNED ) ";


    String observeHasCreateWrapperSql = " select * from (" + observeHasCreateQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(observeHasCreateWrapperSql)
    IPage<ObserveHasCreateInfoVo> observeHasCreatePage(Page<ObserveHasCreateInfoVo> page,@Param("ew") QueryWrapper<ObserveHasCreateInfoVo> wrapper);

    @Select(observeHasCreateWrapperSql)
    List<ObserveHasCreateInfoVo> observeHasCreateList(@Param("ew") QueryWrapper<ObserveHasCreateInfoVo> wrapper);

    String observeHasCreateCountWrapperSql = " select count(*) from (" + observeHasCreateQuerySql + ") as q ${ew.customSqlSegment}";
    @Select(observeHasCreateCountWrapperSql)
    long observeHasCreateCount(@Param("ew") QueryWrapper<ObserveHasCreateInfoVo> wrapper);
}
